USE [master];
GO

IF DB_ID('CurrencyManagerAdminDB') IS NOT NULL DROP DATABASE [CurrencyManagerAdminDB];

CREATE DATABASE [CurrencyManagerAdminDB];
GO

ALTER DATABASE [CurrencyManagerAdminDB] SET COMPATIBILITY_LEVEL = 110;
GO

USE [CurrencyManagerAdminDB];
GO

SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON;
GO


-------------------------------------------------------------------------------------------
----------------------------------------Clients section------------------------------------
-------------------------------------------------------------------------------------------

CREATE SCHEMA [Clients] AUTHORIZATION dbo;
GO

----------------------------
--[Client].Companies table--
----------------------------

CREATE TABLE [Clients].[Company](
    [CompanyId]    INT          NOT NULL IDENTITY(1,1) 
       CONSTRAINT [PK_Company] PRIMARY KEY,
    [CompanyName]  VARCHAR(30)  NOT NULL);
GO  

SET IDENTITY_INSERT [Clients].[Company] ON;
GO        
                       
INSERT INTO [Clients].[Company]([CompanyId], [CompanyName]) VALUES 
            (1, 'Nissan'),
            (2, 'Samsung'),
            (3, 'LG'),
            (4, 'Nokia'),
            (5, 'Atlant');
            
SET IDENTITY_INSERT [Clients].[Company] OFF;
GO


-------------------------------
--[Client].CurrencyList table--
-------------------------------

CREATE TABLE [Clients].[CurrencyList]([CurrencyName] VARCHAR(30) NOT NULL
                                    CONSTRAINT [PK_Currency_List] PRIMARY KEY);
GO

INSERT INTO [Clients].[CurrencyList]([CurrencyName]) VALUES 
            ('USD'),
            ('EUR'),
            ('UAH'),
            ('RUB');
GO
---------------------------
--[Client].Currency table--
---------------------------

CREATE TABLE [Clients].[Currency]([CurrencyId]    INT          NOT NULL IDENTITY(1,1) 
                                    CONSTRAINT [PK_Currency] PRIMARY KEY,
                                  [CurrencyName]  VARCHAR(30)  NOT NULL,
                                  [BankBuy]       MONEY        NULL,
                                  [BankSell]      MONEY        NULL,
                                  [CompanyId]     INT          NOT NULL);

ALTER TABLE [Clients].[Currency] ADD CONSTRAINT [FK_Currency_Company] FOREIGN KEY ([CompanyId]) 
REFERENCES [Clients].[Company]([CompanyId]);
ALTER TABLE [Clients].[Currency] ADD CONSTRAINT [FK_Currency_CurrencyList] FOREIGN KEY ([CurrencyName]) 
REFERENCES [Clients].[CurrencyList]([CurrencyName]);
GO

INSERT INTO [Clients].[Currency]([CurrencyName], [BankBuy], [BankSell], [CompanyId]) VALUES 
            ('USD', 8360, 8380, 1),
            ('EUR', 11990, 12100, 1),
            ('UAH', 890, 1100, 1),
            ('RUB', 275, 280, 1),
            ('USD', 8560, 8600, 2),
            ('EUR', 12100, 12110, 2),
            ('UAH', 950, 1150, 2),
            ('RUB', 278, 283, 2),
            ('USD', 8350, 8420, 3),
            ('EUR', 12020, 12200, 3),
            ('UAH', 1000, 1100, 3),
            ('RUB', 275, 285, 3),
            ('USD', 8500, 8680, 4),
            ('EUR', 12120, 12180, 4),
            ('UAH', 900, 1120, 4),
            ('RUB', 277, 290, 4),
            ('USD', 8490, 8590, 5),
            ('EUR', 11890, 12000, 5),
            ('UAH', 970, 1000, 5),
            ('RUB', 270, 283, 5);
GO
